package jdbcTest2;

//新增貂蝉同学的借阅记录：诗经，从2019年9月25日17:50到2019年10月25日17:50

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class Insert {
    public static void main(String[] args) throws SQLException {
        // 创建数据源
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/ebook?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("Xianmeng");

        // 建立连接
        Connection connection = dataSource.getConnection();

        // 构造SQL + 预处理
        Scanner in = new Scanner(System.in);
        System.out.println("请输入新增的同学名字：");
        String student = in.next();
        System.out.println("请输入借阅的书名");
        String book = in.next();
        System.out.println("请输入借阅开始时间：");
        String start = in.nextLine();
        System.out.println("请输入借阅结束时间：");
        String end = in.nextLine();

        // 先根据书名查找书号book：id name author price category_id
        String sql1 = "select id from book where name = ?";
        PreparedStatement statement1 = connection.prepareStatement(sql1);
        statement1.setString(1,book);
        ResultSet resultSet1 = statement1.executeQuery();
        int id1 = 0;
        while (resultSet1.next()){
            id1 = resultSet1.getInt("id");
        }

        // 根据姓名查询学号id name
        String sql2 = "select id from student where name = ?";
        PreparedStatement statement2 = connection.prepareStatement(sql2);
        statement2.setString(1,student);
        ResultSet resultSet2 = statement2.executeQuery();
        int id2 = 0;
        while (resultSet2.next()){
            id2 = resultSet2.getInt("id");
        }

        // id  book_id student_id start_time end_time
        String sql = "insert into borrow_info values(null, ?, ?, ?, ?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,id1);
        statement.setInt(2,id2);
        statement.setString(3,"2019-9-25 17:50");
        statement.setString(4,"2019-10-25 17:50");
        System.out.println("statement:" + statement);


        // 执行SQL
        int n = statement.executeUpdate();
        System.out.println("影响条数n="+n);

        // 关闭释放
        statement.close();
        connection.close();
    }
}
